﻿--- NOTE ---
--- MỖI THÁNG CẦN UPDATE ---
--- 1. Ngày đầu tháng
--- 2. Bảng chuyển DAO Thẻ các tháng trước, bảng CIF QW, CIF NGoại lệ các tháng trước
--- 3. Cột tháng trong bảng KPIs Target
--- 4. Sửa công thức trong cách lấy KHHĐ tăng thêm
--- 5. Bảng Dư nợ t háng T-3

DECLARE @DATE_NOW NVARCHAR(8) =(SELECT MAX(CONVERT(NVARCHAR(8), BUSINESS_DATE,112)) FROM  SERVER74.BICDATA.DBO.BALANCE_LIVE)
PRINT (@DATE_NOW)
DECLARE @DATE_NOW_1 NVARCHAR(10) = convert(nvarchar(10),(select convert(date, @DATE_NOW)),120)--(SELECT MAX(CONVERT(NVARCHAR(10), BUSINESS_DATE,120)) FROM  SERVER74.BICDATA.DBO.BALANCE_LIVE)
PRINT (@DATE_NOW_1)
DECLARE @STARTOFMONTH NVARCHAR (20)= CONVERT (NVARCHAR(8),(DATEADD(M,DATEDIFF(MONTH, 0, CONVERT(DATE,@DATE_NOW)),0)),112)
PRINT @STARTOFMONTH
DECLARE @STARTOFMONTH_1 NVARCHAR (20)= convert(nvarchar(10),(select convert(date, @STARTOFMONTH)),120)
PRINT @STARTOFMONTH_1
DECLARE @ENDMONTH_LAST_3 VARCHAR(10) = convert(nvarchar(10),dateadd(d, -1,dateadd(m, -2,convert(date, @STARTOFMONTH))),120)
PRINT @ENDMONTH_LAST_3
DECLARE @ENDMONTH_LAST_3_1 VARCHAR(10) = convert(nvarchar(10),dateadd(d, -1,dateadd(m, -2,convert(date, @STARTOFMONTH))),112)
PRINT @ENDMONTH_LAST_3_1
DECLARE @YEARMONTH_LAST_MONTH VARCHAR(6) = CONVERT(NVARCHAR(6),DATEADD(D, -1,@STARTOFMONTH),112)
PRINT @YEARMONTH_LAST_MONTH

--- 1 - Lấy các bảng đầu vào và update thông tin---
print('--- 1.1 --- Lấy bảng Khách hàng')
exec('
select * into SME2017DAILY.DBO.VPB_CUSTOMER_'+@DATE_NOW+'
from M74.BICDATA.DBO.CUSTOMER where SEGMENT not in (''KHCN''','''HH'')
')

print('--- 1.2 --- Lấy bảng KHHĐ (sửa tên bảng, nội dung)')
exec('
select * into SME2017DAILY.DBO.KHHD_'+@DATE_NOW+'
from server12.tranglt9.dbo.active_customer_daily_report
where report_date = '''+@DATE_NOW_1+''' and STATUS = ''ACTIVE''
')

print('--- 1.3 --- Lấy bảng Dư nợ, Huy động Bình quân ---')
exec('
select * into SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme
from GIANGLH3.DAILY.[dbo].[AVGBAL'+@DATE_NOW+']

alter table SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme
add PRODUCT_DETAIL NVARCHAR (255) 

UPDATE SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme 
SET PRODUCT_DETAIL = ''6.3. Unsecured Overdraft''
FROM SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme  AS A , (SELECT * FROM  M74.bicdata.dbo.lntb_disbursement 
WHERE (OVERDRAFT_COLLATERAL = ''N'' OR OVERDRAFT_COLLATERAL IS NULL)) AS b 
WHERE A.ACCTNO collate database_default = B.ACCTNO AND A.PRODUCT_NAME = ''6. Overdraft'' 

--- Cập nhật DAO_CIF
update SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme 
set DAO_CIF = null

UPDATE SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme
	SET DAO_CIF = B.DAO
	FROM SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme A , SME2017DAILY.DBO.VPB_CUSTOMER_'+@DATE_NOW+' B
	WHERE A.CIF collate database_default = B.RECID

alter table SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme 
add RRT_ID nvarchar (100)

UPDATE SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme --- code update thêm RRT_ID từ bảng T24CRD
	SET RRT_ID = B.RRT_ID
	from SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme A, (SELECT CONTRACT_NO, RRT_ID from M16.VPB_WHR2.DBO.T24CRD where business_date ='''+@DATE_NOW_1+''' and ISNUMERIC(RRT_ID) = 1
	AND NOT (RRT_ID LIKE ''%.%'')  AND LEN(RRT_ID) <= 18) B
	where A.ACCTNO collate database_default  = B.CONTRACT_NO 

alter table SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme 
add APP_REJ_DATE date

Update SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme
Set APP_REJ_DATE = B.APP_REJ_DATE
From SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme A, M74.BICDATA.DBO.FINNONE_LIVE B 
where A.RRT_ID COLLATE DATABASE_DEFAULT  = B.APP_ID_C and B.business_date ='''+@DATE_NOW_1+''' and BI_APPSSTATUS = ''APPROVED''

--Update SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme 
--Set APP_REJ_DATE = B.APP_RE_CAN_DATE
--From DSA.DBO.CLOS_20170310 B 
--where RRT_ID COLLATE DATABASE_DEFAULT = B.SZ_APPLICATION_NO

Update SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme
Set APP_REJ_DATE = convert(date,ISSUE_DATE) 
where APP_REJ_DATE is null

alter table SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme
add LOAI_THE nvarchar (40)

select * into #loai_the from M16.[VPB_WHR2].[dbo].[CARD_ISSUE_T24] where limit_ref like ''%06%'' or limit_ref like ''%05%''

update SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme
set LOAI_THE = CASE WHEN SOURCE_ID in (select CARD_ID from #loai_the where limit_ref like ''%05%'') then ''CTSDB''
				WHEN SOURCE_ID in (select CARD_ID from #loai_the where limit_ref like ''%06%'') then ''KTSDB'' end
')

--- 1.4 --- Lấy bảng CIF chuyển BAD BANK trong tháng
exec('
--- Nếu chuyển vào rồi chuyển ra vẫn bị tính thì chạy code như sau
SELECT distinct CIF, DATE_GBBB INTO SME2017DAILY.DBO.BADBANK_'+@DATE_NOW+'
FROM M16.[VPB_G3B].[DBO].[TBL_G3B_T24CRD] 
			WHERE BUSINESS_DATE between '''+@STARTOFMONTH_1+''' and '''+@DATE_NOW_1+'''			
				AND BRANCH_CODE_GBBB = ''VN9990001''
				AND DATE_GBBB IS NOT NULL and DATE_GBBB between '''+@STARTOFMONTH_1+''' and '''+@DATE_NOW_1+'''
')

--- 1.5 --- Lấy bảng LD chuyển BAD BANK trong tháng
exec('
select *, ''YES'' NOXAU into SME2017DAILY.DBO.BALANCE_BADBANK_'+@DATE_NOW+'
from M74.sme.dbo.incentive'+@DATE_NOW+'
where CIF in (select CIF from SME2017DAILY.DBO.BADBANK_'+@DATE_NOW+' where CIF is not null) and NHOMNOMOI in (''3'',''4'',''5'') 
insert into SME2017DAILY.DBO.BALANCE_BADBANK_'+@DATE_NOW+' (ACCTNO, BAL_QD, CIF, DAO, NOXAU)
select ACNT_CONTRACT_ID, case when BALANCE >= 0 then 0 else -BALANCE end, CONTRACT_CIF, T24_DAO,''YES''
from M74.BICDATA.DBO.CARD_LIVE where business_date ='''+@DATE_NOW_1+'''

alter table SME2017DAILY.DBO.BALANCE_BADBANK_'+@DATE_NOW+'
add DAO_CIF nvarchar (50)

update SME2017DAILY.DBO.BALANCE_BADBANK_'+@DATE_NOW+' 
SET DAO_CIF = B.DAO
	FROM SME2017DAILY.DBO.BALANCE_BADBANK_'+@DATE_NOW+'  A , SME2017DAILY.DBO.VPB_CUSTOMER_'+@DATE_NOW+' B
	WHERE A.CIF collate database_default = B.RECID
')

--- 1.6 --- Lấy bảng CARD
exec('
select *  into SME2017DAILY.DBO.CARD_'+@DATE_NOW+'
from M74.BICDATA.DBO.CARD_LIVE
where  BUSINESS_DATE ='''+@DATE_NOW_1+''' AND (CARD_NUMBER LIKE ''559073%'' OR CARD_NUMBER LIKE ''519930%'' or CARD_NUMBER LIKE ''522384%'')

update SME2017DAILY.DBO.CARD_'+@DATE_NOW+' set T24_DAO = B.NEW_DAO
from SME2017DAILY.DBO.CARD_'+@DATE_NOW+' A, SME2017.DBO.CHUYEN_DAO_THE_'+@YEARMONTH_LAST_MONTH+' B
where A.SOURCE_ID = B.SOURCE_ID
')

--- 1.7 --- Update lại bảng KPIs Target
exec('

IF EXISTS(SELECT *
         FROM SME2017DAILY.DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''KPI_TARGET_201704_CHANGE'')
        DROP TABLE SME2017DAILY.dbo.KPI_TARGET_201704_CHANGE

select * into SME2017DAILY.DBO.KPI_TARGET_201704_CHANGE from SME2017.DBO.KPI_TARGET_'+@YEARMONTH_LAST_MONTH+'

update SME2017DAILY.DBO.KPI_TARGET_201704_CHANGE
set apr2017 =  b.kpiS_apr2017
from SME2017DAILY.DBO.KPI_TARGET_201704_CHANGE A, SME2017DAILY.DBO.KPIs_201704 B
where A.DAo = B.DAO and A.CODE = B.CODE and B.Branch_code = A.Branch
')

--- 1.8 --- Update lại KH chuyển giao trong tháng
exec('
drop table #CHUYEN_DAOCIF_201704
select RECID, DAO as OLD_DAO into #CHUYEN_DAOCIF_201704 from SME2017.DBO.VPB_CUSTOMER_20170331

alter table #CHUYEN_DAOCIF_201704
add NEW_DAO nvarchar (50)

update #CHUYEN_DAOCIF_201704
set NEW_DAO = B.DAO 
from #CHUYEN_DAOCIF_201704 A, SME2017DAILY.DBO.VPB_CUSTOMER_'+@DATE_NOW+' B
where A.RECID COLLATE DATABASE_DEFAULT = B.RECID

drop table #tang_KHHD 
select NEW_DAO, count(RECID) CIF into #tang_KHHD
from #CHUYEN_DAOCIF_201704
where OLD_DAO <> NEW_DAO
AND RECID in (select CIF from SME2016.DBO.KHHD_201612)
and RECID in (Select Recid from SME2017DAILY.DBO.VPB_CUSTOMER_'+@DATE_NOW+' where  ((channel in (''DSASME'')  and datediff(day, CUS_OPEN_DATE, '''+@DATE_NOW_1+''')>120 ) or Channel not in   (''BDO1'',''BDO2'',''BDO3'',''BDO4'',''BDO5'',''MBD1'',''MBD2'',''MBD3'',''MBD4'',''MBD5'',''DSASME'') or channel is null)
	AND SEGMENT=''SMES'' )
group by NEW_DAO 

drop table #giam_KHHD 
select OLD_DAO, count(RECID) CIF into #giam_KHHD
from #CHUYEN_DAOCIF_201704
where OLD_DAO <> NEW_DAO
and RECID in (select CIF from SME2016.DBO.KHHD_201612)
and RECID in (Select Recid from SME2017DAILY.DBO.VPB_CUSTOMER_'+@DATE_NOW+' where  ((channel in (''DSASME'')  and datediff(day, CUS_OPEN_DATE, '''+@DATE_NOW_1+''')>120 ) or Channel not in  (''BDO1'',''BDO2'',''BDO3'',''BDO4'',''BDO5'',''MBD1'',''MBD2'',''MBD3'',''MBD4'',''MBD5'',''DSASME'') or channel is null)
	AND SEGMENT=''SMES'' )
group by OLD_DAO

update SME2017.DBO.KPIs_201704
set ban_giao_201704 = B.CIF
from SME2017.DBO.KPIs_201704 a, #giam_KHHD b
where a.DAO collate DATABASE_DEFAULT = B.OLD_DAO and a.code = ''7''

update SME2017.DBO.KPIs_201704
set nhan_ban_giao_201704 = B.CIF
from SME2017.DBO.KPIs_201704 a, #tang_KHHD b
where a.DAO collate DATABASE_DEFAULT = B.NEW_DAO and a.code = ''7''
')

--- 2. --- CHẠY CODE
--- 2.1 --- Lấy mẫu bảng
exec('
select * into SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+'
from SME2017.DBO.FINAL_MSBO_201702
delete SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+'
')

--- 2.2 ---lấy các mã DAO có trong KPI target và nhân sự
exec('
insert into SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' (DAO)
select distinct DAO from SME2017DAILY.DBO.KPI_TARGET_201704_CHANGE --- cập nhật hàng tháng
where (position like ''%MBO%'' or POSITION like''%SBO%'') and DAO is not null  

insert into SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' (DAO)
select distinct DAO from SME2017.DBO.HR_SME_'+@YEARMONTH_LAST_MONTH+'
where (position like ''%MBO%'' or POSITION like''%SBO%'') and DAO is not null and DAO collate database_default not in 
(select convert ( nvarchar,dao,112) from SME2017DAILY.DBO.KPI_TARGET_201704_CHANGE where dao is not null)
')

--- 2.3 ---Update KPI target 
exec('
Update SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+'
SET AVGBAL_LOAN_TARGET = APR2017 ---- Dư nợ BQ -----
From SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' AS A, SME2017DAILY.DBO.KPI_TARGET_201704_CHANGE  AS B
Where A.DAO collate DATABASE_DEFAULT = B.DAO and Code = ''1''

Update SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+'
SET  AVGBAL_DEPOSIT_TARGET= APR2017 ----- Huy động BQ ----
From SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' AS A, SME2017DAILY.DBO.KPI_TARGET_201704_CHANGE  AS B
Where A.DAO collate DATABASE_DEFAULT = B.DAO and Code = ''2''

Update SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+'
SET NEWNPL_LOAN_TARGET = APR2017 --- Nợ xấu ----
From SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' AS A, SME2017DAILY.DBO.KPI_TARGET_201704_CHANGE  AS B
Where A.DAO collate DATABASE_DEFAULT = B.DAO and Code = ''3''

Update SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+'
SET  NFI_TARGET= APR2017 ---- Phí ----
From SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' AS A, SME2017DAILY.DBO.KPI_TARGET_201704_CHANGE  AS B
Where A.DAO collate DATABASE_DEFAULT = B.DAO and Code = ''4''

Update SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+'
SET NII_TARGET = APR2017 ----- Lãi ---
From SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' AS A, SME2017DAILY.DBO.KPI_TARGET_201704_CHANGE  AS B
Where A.DAO collate DATABASE_DEFAULT = B.DAO and Code = ''5''

Update SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+'
SET AVGBAL_UPL_LOAN_TARGET = APR2017 --- Tín chấp BQ----
From SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' AS A, SME2017DAILY.DBO.KPI_TARGET_201704_CHANGE  AS B
Where A.DAO collate DATABASE_DEFAULT = B.DAO and Code = ''6''

Update SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+'
SET CUSTOMER_TARGET = APR2017 ---- Khách hàng hoạt động ----
From SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' AS A, SME2017DAILY.DBO.KPI_TARGET_201704_CHANGE  AS B
Where A.DAO collate DATABASE_DEFAULT = B.DAO and Code = ''7''

Update SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+'
SET CREDIT_CARD_TARGET = APR2017 --- Thẻ ----
From SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' AS A, SME2017DAILY.DBO.KPI_TARGET_201704_CHANGE  AS B
Where A.DAO collate DATABASE_DEFAULT = B.DAO and Code = ''8''
')

--- 2.4 --- Thông tin về nhân viên
exec('
Update SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+'
SET SALE_NAME = B.SALE_NAME,
Branch_code = B.Branch_code,
position = B.position_KPI,
time_work = datediff (day, B.DATE_SALES_start, '''+@DATE_NOW+''')/30, 
REGION = b. REGION,
Staff_id = b.Staff_id,
DEPT = B.POSITION
from SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' as A, SME2017.DBO.HR_SME_'+@YEARMONTH_LAST_MONTH+' as B
where A.DAO collate DATABASE_DEFAULT = B.DAO
')

--- 2.5 --- Tính số thực tế
--- 2.5.1 --- Dư nợ tín chấp:
exec(' 
Update SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+'
set AVGBAL_UPL_LOAN_ACT = B.AMT
from SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' as A, 
(select distinct DAO as DAO, sum(AVGBAL_QD)/1E6 as AMT 
from SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme
where (F is null or F =''B'' or (F= ''Y'' and BRANCH_code =''VN0010338'' and BI_SEGMENT = ''SMEs''))
	and (SEGMENT not in (''KHCN'',''HH'') and (branch_code not in (''VN0010289'',''VN0010344'',''VN0010011'',''VN0010012'',''VN0010006'',''VN0010007'',''VN0010002'',''VN0010003'',''VN0010010'',''VN0010324'',''VN0010325'',''VN0010335'',''VN0010336'',''VN0010005'',''VN0010242'',''VN0010009'',''VN0010001'',''VN0010004'',''VN0010008'',''VN0010260'',''VN0010326'',''VN0010327'',''VN0010245'',''VN0010136'') or (SEGMENT is null and LEFT(sector,1)>1)))
	and APP = ''LOAN'' 
	and DAO is not null
	and CIF collate DATABASE_DEFAULT not in (select cif from SME2017DAILY.DBO.BADBANK_'+@DATE_NOW+' where CIF is not null) 
	and (CHUONG_TRINH_SP is null or CHANNEL is null or (CHUONG_TRINH_SP not in (''LD-13-SME CLEAN'',''LD-07-SME-BIL'',''LD-766-MINI BIL'',''LD-762-SME-BIL2', 'LD-875-SME-BIL AUTO', 'LD-876-SME-BIL TAI CAP'',''LD-934-SME BIL EWS'')
	and CHUONG_TRINH_SP not like ''%BIL%'') or (channel in (''DSASME'')  and datediff(day, APP_REJ_DATE, '''+@DATE_NOW_1+''')>90) or Channel not in (''BDO1'',''BDO2'',''BDO3'',''BDO4'',''BDO5'',''MBD1'',''MBD2'',''MBD3'',''MBD4'',''MBD5'',''DSASME''))
	and (CHUONG_TRINH_SP in (''LD-13-SME CLEAN'',''LD-07-SME-BIL'',''LD-766-MINI BIL'',''LD-762-SME-BIL2'',''LD-875-SME-BIL AUTO'',''LD-876-SME-BIL TAI CAP'',''LD-934-SME BIL EWS'') or 
	(Product_name = ''6. Overdraft'' and PRODUCT_DETAIL =''6.3. Unsecured Overdraft'') or CHUONG_TRINH_SP like ''%BIL%'') and CIF collate DATABASE_DEFAULT not in (select cif_dacthu from SME2017.DBO.CIF_DACTHU_'+@YEARMONTH_LAST_MONTH+' where CIF_DACTHU is not null)
	and (DAO collate DATABASE_DEFAULT = DAO_CIF or CIF collate DATABASE_DEFAULT in (select CIF from SME2017.DBO.CIF_NGOAILE_'+@YEARMONTH_LAST_MONTH+' where CIF is not null))
	group by DAO) as B
where A.DAO collate DATABASE_DEFAULT = B.DAO 

--Cộng số avgbal trong thẻ nếu thẻ là KTSDB --- Nếu tạm thời chưa có bảng dư nợ thẻ thì thôi cuối tháng mới cộng
update SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+'
set AVGBAL_UPL_LOAN_ACT = case when AVGBAL_UPL_LOAN_ACT is null then 0 else AVGBAL_UPL_LOAN_ACT END + case when 
B.Amt is null then 0 else B.Amt END 
from SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' as A, (select distinct DAO, sum(avgbal_QD)/1E6 as amt from SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme
where (DAO COLLATE DATABASE_DEFAULT  = DAO_CIF OR CIF COLLATE DATABASE_DEFAULT IN (SELECT CIF FROM SME2017.DBO.CIF_NGOAILE_'+@YEARMONTH_LAST_MONTH+' where CIF is not null))
AND (LOAI_THE =''KTSDB'' or LOAI_THE is null)
AND APP =''CREDIT CARD''
group by DAO) as B
where A.DAO collate DATABASE_DEFAULT = B.DAO 
')

--- 2.5.2 Dư nợ BQ
exec('
Update SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+'
set AVGBAL_LOAN_ACT = B.AMT
from SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' as A,
(select distinct DAO as DAO, sum(AVGBAL_QD)/1E6 as AMT 
from SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme
where (F is null or F =''B'' or (F= ''Y'' and BRANCH_CODE = ''VN0010338'' and BI_SEGMENT = ''SMEs''))
	AND (SEGMENT not in (''KHCN'',''HH'') and (branch_code not in (''VN0010289'',''VN0010344'',''VN0010011'',''VN0010012'',''VN0010006'',''VN0010007'',''VN0010002'',''VN0010003'',''VN0010010'',''VN0010324'',''VN0010325'',''VN0010335'',''VN0010336'',''VN0010005'',''VN0010242'',''VN0010009'',''VN0010001'',''VN0010004'',''VN0010008'',''VN0010260'',''VN0010326'',''VN0010327'',''VN0010245'',''VN0010136'') or (SEGMENT is null and LEFT(sector,1)>1)))
	and APP = ''LOAN''
	AND ((channel in (''DSASME'')  and datediff(day, APP_REJ_DATE, '''+@DATE_NOW_1+''')>90 ) or Channel not in  (''BDO1'',''BDO2'',''BDO3'',''BDO4'',''BDO5'',''MBD1'',''MBD2'',''MBD3'',''MBD4'',''MBD5'',''DSASME'') or channel is null)
	and DAO is not null
	and CIF collate DATABASE_DEFAULT not in (select cif from SME2017DAILY.DBO.BADBANK_'+@DATE_NOW+' where CIF is not null)
	and CIF collate DATABASE_DEFAULT not in (select cif_dacthu from SME2017.DBO.CIF_DACTHU_'+@YEARMONTH_LAST_MONTH+' where CIF_DACTHU is not null) 
	and (DAO collate DATABASE_DEFAULT = DAO_CIF or CIF collate DATABASE_DEFAULT in (select CIF from SME2017.DBO.CIF_NGOAILE_'+@YEARMONTH_LAST_MONTH+' where CIF is not null))
group by DAO) as B
where A.DAO collate DATABASE_DEFAULT = B.DAO 

--- Cộng số avgbal trong thẻ
update SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+'
set AVGBAL_LOAN_ACT = case when AVGBAL_LOAN_ACT is null then 0 else AVGBAL_LOAN_ACT END + case when 
B.Amt is null then 0 else B.Amt END
from SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' as A, (select distinct DAO, sum(avgbal_QD)/1E6 as amt from SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme
where (DAO COLLATE DATABASE_DEFAULT  = DAO_CIF OR CIF COLLATE DATABASE_DEFAULT IN (SELECT CIF FROM SME2017.DBO.CIF_NGOAILE_'+@YEARMONTH_LAST_MONTH+' where CIF is not null))
AND APP =''CREDIT CARD''
group by DAO) as B
where A.DAO collate DATABASE_DEFAULT = B.DAO 
')

--- 3. Tính tổng huy động 
-- Lấy từ bảng Balance, Thỏa mãn F là null hoặc = B; BI_segment là SMEs;
--- APP là Deposit; DAO ko null; CHANNEL khác 'SMETLS' và CHANNEL khác'PSASME' hoặc channel là null; CIF ko nằm trong CIF đặc thù; 
--DAO = DAO_CIF hoặc CIF nằm trong CIF ngoại lệ;
exec('
Update SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+'
set AVGBAL_DEPOSIT_ACT =  B.AMT
from SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' as A,
(select distinct DAO as DAO, sum(AVGBAL_QD)/1E6 as AMT 
from SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme
where (F is null or F =''B'' or (F= ''Y'' and BRANCH_CODE = ''VN0010338'' and BI_SEGMENT = ''SMEs''))
 AND (SEGMENT not in (''KHCN'',''HH'') and (branch_code not in (''VN0010289'',''VN0010344'',''VN0010011'',''VN0010012'',''VN0010006'',''VN0010007'',''VN0010002'',''VN0010003'',''VN0010010'',''VN0010324'',''VN0010325'',''VN0010335'',''VN0010336'',''VN0010005'',''VN0010242'',''VN0010009'',''VN0010001'',''VN0010004'',''VN0010008'',''VN0010260'',''VN0010326'',''VN0010327'',''VN0010245'',''VN0010136'') or (SEGMENT is null and LEFT(sector,1)>1)))
and APP = ''DEPOSIT''
and DAO is not null
and (channel <> ''SMETLS'' and channel <> ''PSASME'' or channel is null)  ---Channel CTV 
and CIF collate DATABASE_DEFAULT not in (select cif_dacthu from SME2017.DBO.CIF_DACTHU_'+@YEARMONTH_LAST_MONTH+'  where CIF_DACTHU is not null)
and (DAO collate DATABASE_DEFAULT = DAO_CIF or CIF collate DATABASE_DEFAULT in (select CIF from SME2017.DBO.CIF_NGOAILE_'+@YEARMONTH_LAST_MONTH+' where CIF is not null))
group by DAO) as B
where A.DAO collate DATABASE_DEFAULT = B.DAO 
')

--- 2.5.4 Tính NPL:
---Bảng Nợ xấu
exec('
drop table SME2017DAILY.DBO.NPL_'+@DATE_NOW+'
Select distinct DAO into SME2017DAILY.DBO.NPL_'+@DATE_NOW+' from  SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' 

alter table SME2017DAILY.DBO.NPL_'+@DATE_NOW+'
add NO_XAU_T float,
	DU_NO_THANG_T3 float,
	TI_LE float

Update SME2017DAILY.DBO.NPL_'+@DATE_NOW+'
set NO_XAU_T = B.AMT
FROM  SME2017DAILY.DBO.NPL_'+@DATE_NOW+' AS A, 
	(SELECT DISTINCT DAO DAO ,isnull(SUM(CASE WHEN NOXAU = ''YES'' THEN ISNULL(BAL_QD,0) END),0) AMT 
	FROM SME2017DAILY.DBO.BALANCE_BADBANK_'+@DATE_NOW+'  
	WHERE ACCTNO COLLATE DATABASE_DEFAULT in (select ACCTNO from SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme where (F IS NULL OR F =''B'' or (F= ''Y'' and BRANCH_CODE = ''VN0010338'' and BI_SEGMENT = ''SMEs''))
	AND DAO IS NOT NULL
	AND (SEGMENT not in (''KHCN'',''HH'') and (branch_code not in (''VN0010289'',''VN0010344'',''VN0010011'',''VN0010012'',''VN0010006'',''VN0010007'',''VN0010002'',''VN0010003'',''VN0010010'',''VN0010324'',''VN0010325'',''VN0010335'',''VN0010336'',''VN0010005'',''VN0010242'',''VN0010009'',''VN0010001'',''VN0010004'',''VN0010008'',''VN0010260'',''VN0010326'',''VN0010327'',''VN0010245'',''VN0010136'') or (SEGMENT is null and LEFT(sector,1)>1)))
	AND APP=''LOAN''
	AND CIF COLLATE DATABASE_DEFAULT NOT IN (SELECT CIF FROM SME2016.DBO.BADBANK_20161231)
	AND CIF COLLATE DATABASE_DEFAULT NOT IN (SELECT CIF_DACTHU FROM SME2017.DBO.CIF_DACTHU_'+@YEARMONTH_LAST_MONTH+' where CIF_DACTHU is not null) 
	AND (CHUONG_TRINH_SP IS NULL or (channel in (''DSASME'')  and datediff(day, APP_REJ_DATE, '''+@DATE_NOW_1+''')>90 ) or Channel not in  (''BDO1'',''BDO2'',''BDO3'',''BDO4'',''BDO5'',''MBD1'',''MBD2'',''MBD3'',''MBD4'',''MBD5'',''DSASME'') or channel is null)
	AND ( DAO COLLATE DATABASE_DEFAULT  = DAO_CIF OR CIF COLLATE DATABASE_DEFAULT IN (SELECT CIF FROM  SME2017.DBO.CIF_NGOAILE_'+@YEARMONTH_LAST_MONTH+' where CIF is not null)))
	GROUP BY DAO )  AS B
	WHERE A.DAO COLLATE DATABASE_DEFAULT = B.DAO 

Update SME2017DAILY.DBO.NPL_'+@DATE_NOW+' --- xin code update vào
set DU_NO_THANG_T3 = B.AMT
FROM  SME2017DAILY.DBO.NPL_'+@DATE_NOW+' AS A, 
	(SELECT DISTINCT DAO DAO ,ISNULL(SUM(ISNULL(BAL_QD,0)),0) AMT 
	FROM SME2017.DBO.BALANCE'+@ENDMONTH_LAST_3_1+'_SME 
	WHERE (F IS NULL OR F =''B'' or (F= ''Y'' and BRANCH_CIF = ''VN0010338'' and BI_SEGMENT = ''SMEs''))
	AND DAO IS NOT NULL
	AND (SEGMENT not in (''KHCN'',''HH'') and (branch_code not in (''VN0010289'',''VN0010344'',''VN0010011'',''VN0010012'',''VN0010006'',''VN0010007'',''VN0010002'',''VN0010003'',''VN0010010'',''VN0010324'',''VN0010325'',''VN0010335'',''VN0010336'',''VN0010005'',''VN0010242'',''VN0010009'',''VN0010001'',''VN0010004'',''VN0010008'',''VN0010260'',''VN0010326'',''VN0010327'',''VN0010245'',''VN0010136'') or (SEGMENT is null and LEFT(sector,1)>1)))
	AND APP=''LOAN''
	AND CIF COLLATE DATABASE_DEFAULT NOT IN (SELECT CIF FROM SME2017.DBO.BADBANK_'+@ENDMONTH_LAST_3_1+')
	AND CIF COLLATE DATABASE_DEFAULT NOT IN (SELECT CIF_DACTHU FROM SME2017.DBO.CIF_DACTHU_201701 where CIF_DACTHU is not null) 
	AND  (CHUONG_TRINH_SP IS NULL or (channel in (''DSASME'')  and datediff(day, APP_REJ_DATE, '''+@ENDMONTH_LAST_3+''')>90 ) or Channel not in (''BDO1'',''BDO2'',''BDO3'',''BDO4'',''BDO5'',''MBD1'',''MBD2'',''MBD3'',''MBD4'',''MBD5'',''DSASME'') or channel is null)
	AND ( DAO COLLATE DATABASE_DEFAULT  = DAO_CIF OR CIF COLLATE DATABASE_DEFAULT IN (SELECT CIF FROM  SME2017.DBO.CIF_NGOAILE_201701 where CIF is not null))
	GROUP BY DAO )  AS B
	WHERE A.DAO COLLATE DATABASE_DEFAULT = B.DAO 

	update SME2017DAILY.DBO.NPL_'+@DATE_NOW+'
	set DU_NO_THANG_T3 = case when DU_NO_THANG_T3 is null then 0 else DU_NO_THANG_T3 END + case when 
	B.Amt is null then 0 else B.Amt END
	from SME2017DAILY.DBO.NPL_'+@DATE_NOW+' as A, (select distinct T24_DAO as DAO, sum( case when balance >= 0 then 0 
	when balance is null then 0 when balance < 0 then balance* -1 end)/1E6 as amt from SME2017.DBO.CARD_'+@ENDMONTH_LAST_3_1+' where BUSINESS_DATE ='''+@ENDMONTH_LAST_3+'''
	and CARD_NUMBER not in (select CARD_NUMBER from SME2017.DBO.THE_DSA where CARD_NUMBER is not null and datediff(day, UNLOCK_DATE, '''+@ENDMONTH_LAST_3+''')<90)
	AND ( t24_DAO COLLATE DATABASE_DEFAULT  = DAO_CIF OR CONTRACT_CIF COLLATE DATABASE_DEFAULT IN (SELECT CIF FROM SME2017.DBO.CIF_NGOAILE_201701 where CIF is not null))
	group by T24_DAO) as B
	where A.DAO collate DATABASE_DEFAULT = B.DAO

update SME2017DAILY.DBO.NPL_'+@DATE_NOW+' set TI_LE = ISNULL(NO_XAU_T,0)/isnull(DU_NO_THANG_T3,0) where DU_NO_THANG_T3 is not null and DU_NO_THANG_T3 <> 0

UPDATE SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+'
	SET NEWNPL_LOAN_ACT = case when B.DU_NO_THANG_T3 is null and B.NO_XAU_T > 0 then 1 else B.TI_LE end
	FROM  SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' AS A, SME2017DAILY.DBO.NPL_'+@DATE_NOW+'  AS B
	WHERE A.DAO COLLATE DATABASE_DEFAULT = B.DAO
')

--- 2.5.5 --- Thẻ
exec('
Drop table #the_mo_moi
Drop table #the_dong

CREATE TABLE #the_mo_moi
(CUS NVARCHAR (100), 
DAO NVARCHAR (25), 
POINT FLOAT, 
TYPE NVARCHAR (25),
DAO_CIF NVARCHAR (25)
)

CREATE TABLE #the_dong
(CUS NVARCHAR (100), 
DAO NVARCHAR (25), 
POINT FLOAT, 
TYPE NVARCHAR (25),
DAO_CIF NVARCHAR (25)
)
----MỞ THẺ----
--- Thẻ Credit---

SELECT CONVERT (NVARCHAR, CONTRACT_CIF,112) CONTRACT_CIF, T24_DAO , CONTRACT_NUMBER, CASE WHEN COUNT (DISTINCT ( CARD_NUMBER) ) >3 THEN 3
ELSE COUNT (DISTINCT ( CARD_NUMBER) )   END as AMT, ''CREDITCARD'' as TYPE into #The_tin_dung
	FROM SME2017DAILY.DBO.CARD_'+@DATE_NOW+'
	WHERE (CARD_NUMBER LIKE ''559073%'' OR CARD_NUMBER LIKE ''519930%'') ---- Mã đầu thẻ Credit của SMEs
	AND CARD_STATUS = ''CARD OK''
	AND CONTRACT_STATUS = ''Account OK''
	AND T24_DAO IS NOT NULL
	AND CONVERT(DATE,CARD_DATE_OPEN) >= ''20150101'' ---- Không mở trước 2015, trước đó đã đc tính rồi.
	AND CONVERT(DATE,UNLOCK_DATE) BETWEEN ''20170101'' AND ''+@DATE_NOW+'' --- từ đầu 2017 đến nay
	AND PRODUCTION_STATUS =''READY'' AND SOURCE_INFO NOT LIKE ''%SMETLS%'' AND SOURCE_INFO NOT LIKE ''%PSASME%''
	AND (BI_CHANNEL NOT IN (''PSASME'') OR BI_CHANNEL IS NULL)
	and CARD_NUMBER not in (select CARD_NUMBER from SME2017.DBO.THE_DSA where CARD_NUMBER is not null)
	GROUP BY CONTRACT_CIF, CONTRACT_NUMBER, T24_DAO  

INSERT INTO #the_mo_moi (CUS, DAO, POINT, type)
SELECT CONVERT (NVARCHAR, CONTRACT_CIF,112) , T24_DAO , CASE WHEN sum(isnull(AMT,0)) >6 THEN 6
ELSE sum(isnull(AMT,0))   END, ''CREDITCARD'' 
	FROM #The_tin_dung
	GROUP BY CONTRACT_CIF, T24_DAO
	
--- Thẻ Debit ---
INSERT INTO #the_mo_moi (CUS, DAO, POINT, type)
SELECT CONTRACT_CIF, T24_DAO , CASE WHEN COUNT (DISTINCT (CARD_NUMBER)) > 2 THEN 1
ELSE COUNT (DISTINCT (CARD_NUMBER))*0.5   END AMT , ''DEBITCARD''
	FROM SME2017DAILY.DBO.CARD_'+@DATE_NOW+'
	WHERE CARD_NUMBER LIKE ''522384%''
	AND CARD_STATUS = ''CARD OK''
	AND CONTRACT_STATUS = ''Account OK''
	AND T24_DAO IS NOT NULL
	AND CONVERT(DATE,CARD_DATE_OPEN) >= ''20150101'' 
	AND CONVERT(DATE,UNLOCK_DATE) BETWEEN ''20170101'' AND ''+@DATE_NOW+''---  từ đầu 2017 đến nay
	AND PRODUCTION_STATUS =''READY'' AND SOURCE_INFO NOT LIKE ''%SMETLS%'' AND SOURCE_INFO NOT LIKE ''%PSASME%''
	AND (BI_CHANNEL NOT IN (''PSASME'') OR BI_CHANNEL IS NULL)
	and CARD_NUMBER not in (select CARD_NUMBER from SME2017.DBO.THE_DSA where CARD_NUMBER is not null)
	GROUP BY CONTRACT_CIF, T24_DAO  

--- Cập nhật DAO_CIF từ bảng CUSTOMER ----
UPDATE #the_mo_moi  
	SET DAO_CIF = A.DAO 
	FROM #the_mo_moi  AS B , SME2017.DBO.VPB_CUSTOMER_'+@ENDMONTH_LAST_3_1+' AS A
	WHERE A.RECID COLLATE DATABASE_DEFAULT = B.CUS

--- THẺ ĐÓNG---
--- Thẻ Credit---

SELECT CONVERT (NVARCHAR, CONTRACT_CIF,112) CONTRACT_CIF, T24_DAO , CONTRACT_NUMBER, CASE WHEN COUNT (DISTINCT ( CARD_NUMBER) ) >3 THEN 3
ELSE COUNT (DISTINCT ( CARD_NUMBER) )   END AMT, ''CREDITCARD'' TYPE into #The_tin_dung_dong
	FROM SME2017.DBO.CARD_ENDMONTH_201701
	WHERE (CARD_NUMBER LIKE ''559073%'' OR CARD_NUMBER LIKE ''519930%'') ---- Mã đầu thẻ Credit của SMEs
	AND T24_DAO IS NOT NULL
	AND((CONVERT(DATE,CARD_DATE_OPEN) >= ''2016-10-01'' and ACTUAL_DATE_EXPIRE <= ''2017-03-31'' and ACTUAL_DATE_EXPIRE >= ''2017-01-01'') or (CONVERT(DATE,CARD_DATE_OPEN) >= ''2017-01-01''))
	AND ACTUAL_DATE_EXPIRE is not null and ACTUAL_DATE_EXPIRE <= '''+@DATE_NOW_1+'''
	AND PRODUCTION_STATUS =''READY'' AND SOURCE_INFO NOT LIKE ''%SMETLS%'' AND SOURCE_INFO NOT LIKE ''%PSASME%''
	AND (BI_CHANNEL NOT IN (''PSASME'') OR BI_CHANNEL IS NULL)
	and CARD_NUMBER not in (select CARD_NUMBER from SME2017.DBO.THE_DSA where CARD_NUMBER is not null)
	GROUP BY CONTRACT_CIF, CONTRACT_NUMBER, T24_DAO  

INSERT INTO #the_dong (CUS, DAO, POINT, TYPE)
SELECT CONVERT (NVARCHAR, CONTRACT_CIF,112), T24_DAO , CASE WHEN sum(isnull(AMT,0)) >6 THEN 6
ELSE sum(isnull(AMT,0))    END AMT, ''CREDITCARD''
	FROM #The_tin_dung_dong
	GROUP BY CONTRACT_CIF, T24_DAO  

--- Thẻ Debit ---
INSERT INTO #the_dong (CUS, DAO, POINT, TYPE)
SELECT CONTRACT_CIF, T24_DAO , CASE WHEN COUNT (DISTINCT (CARD_NUMBER)) > 2 THEN 1
ELSE COUNT (DISTINCT (CARD_NUMBER))*0.5   END, ''DEBITCARD'' 
	FROM SME2017.DBO.CARD_ENDMONTH_201701
	WHERE CARD_NUMBER LIKE ''522384%''
	AND T24_DAO IS NOT NULL
	AND CONVERT(DATE,CARD_DATE_OPEN) >= ''20150101'' 
	AND((CONVERT(DATE,CARD_DATE_OPEN) >= ''2016-10-01'' and ACTUAL_DATE_EXPIRE <= ''2017-03-31'' and ACTUAL_DATE_EXPIRE >= ''2017-01-01'') or (CONVERT(DATE,CARD_DATE_OPEN) >= ''2017-01-01''))
	AND ACTUAL_DATE_EXPIRE is not null and ACTUAL_DATE_EXPIRE <= '''+@DATE_NOW_1+'''
	AND PRODUCTION_STATUS =''READY'' AND SOURCE_INFO NOT LIKE ''%SMETLS%'' AND SOURCE_INFO NOT LIKE ''%PSASME%''
	AND (BI_CHANNEL NOT IN (''PSASME'') OR BI_CHANNEL IS NULL)
	and CARD_NUMBER not in (select CARD_NUMBER from SME2017.DBO.THE_DSA where CARD_NUMBER is not null)
	GROUP BY CONTRACT_CIF, T24_DAO  

--- Cập nhật DAO_CIF từ bảng CUSTOMER ----
UPDATE #the_dong  
	SET DAO_CIF = A.DAO 
	FROM #the_dong  AS B , SME2017DAILY.DBO.VPB_CUSTOMER_'+@DATE_NOW+' AS A
	WHERE A.RECID COLLATE DATABASE_DEFAULT = B.CUS

---- Update thông tin thẻ thực tế vào bảng FINAL_MSBO---
UPDATE SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' 
	SET CREDIT_CARD_ACT = isnull(B.AMT,0) 
	FROM SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+'  AS A, 
	(SELECT DISTINCT CONVERT(NVARCHAR(5),DAO) DAO, SUM(POINT) AMT
	FROM #the_mo_moi WHERE DAO IS NOT NULL
	AND (DAO_CIF = DAO)
	GROUP BY CONVERT(NVARCHAR(5),DAO)) AS B
WHERE A.DAO COLLATE DATABASE_DEFAULT = B.DAO

UPDATE SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+'  
	SET CREDIT_CARD_ACT = isnull(CREDIT_CARD_ACT,0) - isnull(C.AMT,0) 
	FROM SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+'  AS A, 
	(SELECT DISTINCT CONVERT(NVARCHAR(5),DAO) DAO, SUM(POINT) AMT
	FROM #the_dong WHERE DAO IS NOT NULL
	AND (DAO_CIF = DAO)
	GROUP BY CONVERT(NVARCHAR(5),DAO)) AS C
	WHERE  A.DAO COLLATE DATABASE_DEFAULT = C.DAO
	
')

--- 2.5.6 --- Khách hàng hoạt động tăng thêm
exec('

DROP TABLE SME2017DAILY.DBO.KHACH_HANG_'+@DATE_NOW+' 
Select distinct DAO into SME2017DAILY.DBO.KHACH_HANG_'+@DATE_NOW+'  from SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' 

alter table SME2017DAILY.DBO.KHACH_HANG_'+@DATE_NOW+' 
add SO_LUY_KE float,
	DEC_2016 float,
	BAN_GIAO float,
	NHAN_BAN_GIAO float,
	SO_MOI float

update SME2017DAILY.DBO.KHACH_HANG_'+@DATE_NOW+' 
set Dec_2016 = CUSTOMER_ACT
from SME2017DAILY.DBO.KHACH_HANG_'+@DATE_NOW+'  A, SME2016.DBO.KPI_2017_DAU_KY B
where A.DAO COLLATE DATABASE_DEFAULT = B.DAO

update SME2017DAILY.DBO.KHACH_HANG_'+@DATE_NOW+' 
set SO_LUY_KE = B.AMOUNT
from SME2017DAILY.DBO.KHACH_HANG_'+@DATE_NOW+'  A, (SELECT DISTINCT  DAO, COUNT (DISTINCT RECID) AMOUNT
	FROM SME2017DAILY.DBO.VPB_CUSTOMER_'+@DATE_NOW+'
	WHERE  RECID COLLATE DATABASE_DEFAULT IN
	(SELECT CIF FROM SME2017DAILY.DBO.KHHD_'+@DATE_NOW+') 
	AND DAO IS NOT NULL
	AND  ((channel in (''DSASME'')  and datediff(day, CUS_OPEN_DATE, '''+@DATE_NOW_1+''')>120 ) or Channel not in (''BDO1'',''BDO2'',''BDO3'',''BDO4'',''BDO5'',''MBD1'',''MBD2'',''MBD3'',''MBD4'',''MBD5'',''DSASME'') or channel is null)
	and (SEGMENT not in (''KHCN'',''HH'') and (COMPANY_BOOK not in (''VN0010289'',''VN0010344'',''VN0010011'',''VN0010012'',''VN0010006'',''VN0010007'',''VN0010002'',''VN0010003'',''VN0010010'',''VN0010324'',''VN0010325'',''VN0010335'',''VN0010336'',''VN0010005'',''VN0010242'',''VN0010009'',''VN0010001'',''VN0010004'',''VN0010008'',''VN0010260'',''VN0010326'',''VN0010327'',''VN0010245'',''VN0010136'') or (SEGMENT is null and LEFT(sector,1)>1)))
	GROUP BY DAO) AS B
where A.DAO COLLATE DATABASE_DEFAULT = B.DAO

update SME2017DAILY.DBO.KHACH_HANG_'+@DATE_NOW+' 
set BAN_GIAO = isnull(B.BAN_GIAO_201701,0) + isnull(B.BAN_GIAO_201702,0) + isnull(B.BAN_GIAO_201703,0) + isnull(B.BAN_GIAO_201704,0)
from SME2017DAILY.DBO.KHACH_HANG_'+@DATE_NOW+'  A, SME2017.DBO.KPIs_201704 B
where A.DAO COLLATE DATABASE_DEFAULT = B.DAO and B.CODE = ''7''

update SME2017DAILY.DBO.KHACH_HANG_'+@DATE_NOW+' 
set NHAN_BAN_GIAO = isnull(B.NHAN_BAN_GIAO_201701,0)+ isnull(B.NHAN_BAN_GIAO_201702,0) + isnull(B.NHAN_BAN_GIAO_201703,0) + isnull(B.NHAN_BAN_GIAO_201704,0)
from SME2017DAILY.DBO.KHACH_HANG_'+@DATE_NOW+' A, SME2017.DBO.KPIs_201704 B
where A.DAO COLLATE DATABASE_DEFAULT = B.DAO and B.CODE = ''7''

update SME2017DAILY.DBO.KHACH_HANG_'+@DATE_NOW+' 
set SO_MOI = isnull(SO_LUY_KE,0) - isnull(DEC_2016,0) + isnull(BAN_GIAO,0) - isnull (NHAN_BAN_GIAO,0) 

UPDATE SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+'  
	SET CUSTOMER_ACT = B.SO_MOI
	FROM SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+'    AS A, 
	SME2017DAILY.DBO.KHACH_HANG_'+@DATE_NOW+'  B
	WHERE A.DAO COLLATE DATABASE_DEFAULT = B.DAO
	
')

--- 2.6 --- Cập nhật lại các dòng NULL
exec('
UPDATE SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' 
SET AVGBAL_UPL_LOAN_TARGET   = ''0'' WHERE AVGBAL_UPL_LOAN_TARGET   IS NULL 

UPDATE SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' 
SET AVGBAL_LOAN_TARGET  = ''0'' WHERE AVGBAL_LOAN_TARGET  IS NULL 

UPDATE SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' 
SET AVGBAL_DEPOSIT_TARGET  = ''0'' WHERE AVGBAL_DEPOSIT_TARGET  IS NULL 

UPDATE SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' 
SET NFI_TARGET  = ''0'' WHERE NFI_TARGET  IS NULL 

UPDATE SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' 
SET NII_TARGET  = ''0'' WHERE NII_TARGET  IS NULL 

UPDATE SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' 
SET NEWNPL_LOAN_TARGET  = ''0'' WHERE NEWNPL_LOAN_TARGET  IS NULL 

UPDATE SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' 
SET CREDIT_CARD_TARGET  = ''0'' WHERE CREDIT_CARD_TARGET  IS NULL 

UPDATE SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' 
SET CUSTOMER_TARGET   = ''0'' WHERE CUSTOMER_TARGET   IS NULL

--- Cập nhật các KPI actual đang bị NULL thành 0 ---
UPDATE SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' 
SET AVGBAL_UPL_LOAN_ACT   = ''0'' WHERE AVGBAL_UPL_LOAN_ACT   IS NULL 

UPDATE SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' 
SET AVGBAL_LOAN_ACT  = ''0'' WHERE AVGBAL_LOAN_ACT  IS NULL 

UPDATE SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' 
SET AVGBAL_DEPOSIT_ACT  = ''0'' WHERE AVGBAL_DEPOSIT_ACT  IS NULL 

UPDATE SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' 
SET NFI_ACT  = ''0'' WHERE NFI_ACT  IS NULL 

UPDATE SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' 
SET NII_ACT  = ''0'' WHERE NII_ACT  IS NULL 

UPDATE SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' 
SET NEWNPL_LOAN_ACT  = ''0'' WHERE NEWNPL_LOAN_ACT  IS NULL 

UPDATE SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' 
SET CREDIT_CARD_ACT  = ''0'' WHERE CREDIT_CARD_ACT  IS NULL OR CREDIT_CARD_ACT  < 0

UPDATE SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+' 
SET CUSTOMER_ACT   = ''0'' WHERE CUSTOMER_ACT   IS NULL OR CUSTOMER_ACT < 0

')


--- 2.7 --- Tính điểm hoàn thành KPIs: Lấy thực tế chia kế hoạch, Max là 1.5, riêng nợ xấu = 1-(Thực tế - Kế hoạch)/Kế hoạch
exec(' 
UPDATE SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+'   
SET AVGBAL_UPL_LOAN_REAL = ( CASE WHEN AVGBAL_UPL_LOAN_TARGET = 0 AND AVGBAL_UPL_LOAN_ACT = 0 THEN 1
	WHEN AVGBAL_UPL_LOAN_TARGET = 0 AND AVGBAL_UPL_LOAN_ACT > 0 THEN 1.5
	WHEN AVGBAL_UPL_LOAN_ACT / AVGBAL_UPL_LOAN_TARGET > 1.5 THEN 1.5 ELSE AVGBAL_UPL_LOAN_ACT / AVGBAL_UPL_LOAN_TARGET END ) ,

	AVGBAL_LOAN_REAL = ( CASE WHEN AVGBAL_LOAN_TARGET = 0 AND AVGBAL_LOAN_ACT = 0 THEN 1
	WHEN AVGBAL_LOAN_TARGET = 0 AND AVGBAL_LOAN_ACT > 0 THEN 1.5
	WHEN AVGBAL_LOAN_ACT / AVGBAL_LOAN_TARGET > 1.5 THEN 1.5 ELSE AVGBAL_LOAN_ACT / AVGBAL_LOAN_TARGET END ) ,

	AVGBAL_DEPOSIT_REAL = ( CASE WHEN AVGBAL_DEPOSIT_TARGET = 0 AND AVGBAL_DEPOSIT_ACT = 0 THEN 1
	WHEN AVGBAL_DEPOSIT_TARGET = 0 AND AVGBAL_DEPOSIT_ACT > 0 THEN 1.5
	WHEN AVGBAL_DEPOSIT_ACT / AVGBAL_DEPOSIT_TARGET > 1.5 THEN 1.5 ELSE AVGBAL_DEPOSIT_ACT / AVGBAL_DEPOSIT_TARGET END ) ,

	NFI_REAL = ( CASE WHEN NFI_TARGET = 0 AND NFI_ACT = 0 THEN 1
	WHEN NFI_TARGET = 0 AND NFI_ACT > 0 THEN 1.5
	WHEN NFI_TARGET = 0 AND NFI_ACT < 0 THEN 0
	WHEN NFI_ACT / NFI_TARGET < 0 THEN 0 
	WHEN NFI_ACT / NFI_TARGET > 1.5 THEN 1.5 ELSE NFI_ACT / NFI_TARGET END ) ,

	NII_REAL = ( CASE WHEN NII_TARGET = 0 AND NII_ACT = 0 THEN 1
	WHEN NII_TARGET = 0 AND NII_ACT > 0 THEN 1.5
	WHEN NII_TARGET = 0 AND NII_ACT < 0 THEN 0
	WHEN NII_ACT / NII_TARGET < 0 THEN 0
	WHEN NII_ACT / NII_TARGET > 1.5 THEN 1.5 ELSE NII_ACT / NII_TARGET END ) ,

	NEWNPL_LOAN_REAL =(CASE WHEN NEWNPL_LOAN_TARGET =0 AND NEWNPL_LOAN_ACT =0 THEN 1 
	WHEN NEWNPL_LOAN_TARGET =0 AND NEWNPL_LOAN_ACT > 0 THEN 0
	WHEN NEWNPL_LOAN_TARGET >0 AND NEWNPL_LOAN_ACT = 0 THEN 1.5
	WHEN 1 - (NEWNPL_LOAN_ACT - NEWNPL_LOAN_TARGET)/NEWNPL_LOAN_TARGET < 0 THEN 0 
	WHEN 1 - (NEWNPL_LOAN_ACT - NEWNPL_LOAN_TARGET)/NEWNPL_LOAN_TARGET >1.5 THEN 1.5 
	ELSE 1 - (NEWNPL_LOAN_ACT - NEWNPL_LOAN_TARGET)/NEWNPL_LOAN_TARGET END ),

	CREDIT_CARD_REAL = ( CASE WHEN CREDIT_CARD_TARGET = 0 AND CREDIT_CARD_ACT = 0 THEN 1
	WHEN CREDIT_CARD_TARGET = 0 AND CREDIT_CARD_ACT > 0 THEN 1.5
	WHEN CREDIT_CARD_ACT / CREDIT_CARD_TARGET > 1.5 THEN 1.5 ELSE CREDIT_CARD_ACT / CREDIT_CARD_TARGET END),

	CUSTOMER_REAL = ( CASE WHEN CUSTOMER_TARGET = 0 AND CUSTOMER_ACT = 0 THEN 1
	WHEN CUSTOMER_TARGET = 0 AND CUSTOMER_ACT > 0 THEN 1.5
	WHEN CUSTOMER_ACT / CUSTOMER_TARGET > 1.5 THEN 1.5 ELSE CUSTOMER_ACT / CUSTOMER_TARGET END  )  
	
')

--- 2.8 --- Tính điểm hoàn thành KPI tổng: lấy từng điểm thành phần nhân trọng số---
exec('
UPDATE SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+'     
		SET KPI_FINAL = (AVGBAL_UPL_LOAN_REAL *0.2 +AVGBAL_LOAN_REAL *0.15 +AVGBAL_DEPOSIT_REAL *0.05 
			 +NFI_REAL *0.15 +NII_REAL*0.2 +NEWNPL_LOAN_REAL*0.05 +CREDIT_CARD_REAL*0.05 +CUSTOMER_REAL *0.15 )  
		WHERE DEPT = ''MBO''

UPDATE SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+'   
		SET KPI_FINAL = (AVGBAL_UPL_LOAN_REAL *0.15 +AVGBAL_LOAN_REAL *0.2 +AVGBAL_DEPOSIT_REAL *0.05 
			 +NFI_REAL *0.15 +NII_REAL*0.25 +NEWNPL_LOAN_REAL*0.05 +CUSTOMER_REAL *0.15)  
		WHERE DEPT = ''SBO''

--- làm tròn điểm tính Incentive ----
UPDATE SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+'   
		SET KPI_ROUND = ROUND (KPI_FINAL*100,0) 
		
')

--- 3 --- In ra bảng KQ ---
exec('
select '''+@DATE_NOW_1+''' BUSINESS_DATE
	   BRANCH_CODE
	  ,SALE_NAME  
      ,DAO 
      ,POSITION  
	  ,TIME_WORK 
      ,REGION 
      ,STAFF_ID 
      , DEPT
	  ,AVGBAL_LOAN_TARGET 
	  ,AVGBAL_DEPOSIT_TARGET 
	  ,NEWNPL_LOAN_TARGET 
      ,AVGBAL_UPL_LOAN_TARGET  
	  ,CUSTOMER_TARGET 
      ,CREDIT_CARD_TARGET 
      ,AVGBAL_LOAN_ACT  
	  ,AVGBAL_DEPOSIT_ACT  
	  ,NEWNPL_LOAN_ACT 
      ,AVGBAL_UPL_LOAN_ACT 
	  ,CUSTOMER_ACT
      ,CREDIT_CARD_ACT 
      ,AVGBAL_LOAN_REAL 
	  ,AVGBAL_DEPOSIT_REAL 
	  ,NEWNPL_LOAN_REAL 
      ,AVGBAL_UPL_LOAN_REAL  
	  ,CUSTOMER_REAL 
      ,CREDIT_CARD_REAL
      ,KPI_FINAL
      ,KPI_ROUND
	  into SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+'_KQ
	  FROM SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+'
	  where AVGBAL_UPL_LOAN_TARGET > 0
	  and AVGBAL_LOAN_TARGET > 0
	  and AVGBAL_DEPOSIT_TARGET > 0
	  and sale_name is not null
	  and position is not null
	  and dao collate database_default  not in (N''Không có mã DAO'',N''Không có mã DAO'')
	  and dao collate database_default in (select dao from SME2017.DBO.HR_SME_'+@YEARMONTH_LAST_MONTH+' where contract_status =N''Chính thức'')
	  and dao collate database_default in (select dao from SME2017DAILY.DBO.KPI_TARGET_201704_CHANGE)
	  and BRANCH_CODE not in (''HCM'', ''HBH'', ''SGN'', ''GDH'', ''PMH'', ''TPU'') 
	  order by REGION, branch_code, DAO
	  
')

--- 4 --- Lấy dữ liệu thô
--- 4.1 DƯ NỢ TÍN CHẤP 
exec('
SELECT ''+@DATE_NOW+'' BUSINESS_DATE, BRANCH_CODE, APP, DAO ,DAO_CIF ,ACCTNO, CIF, AVGBAL_QD,PRODUCT_GROUP, CHUONG_TRINH_SP ,convert(date, ISSUE_DATE)  ISSUE_DATE, CHANNEL,
	(CASE WHEN DAO <> DAO_CIF  AND CIF COLLATE DATABASE_DEFAULT NOT IN
	(SELECT CIF FROM  SME2017.DBO.CIF_NGOAILE_'+@YEARMONTH_LAST_MONTH+' where CIF is not null) THEN ''KHONG GHI NHAN DO DAO CIF KHAC DAO SP'' ELSE '''' END ) NOTE
	into SME2017DAILY.dbo.RAW_DU_NO_TIN_CHAP_BQ_'+@DATE_NOW+'
From SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme
	WHERE  (F is null or F =''B'' or (F= ''Y'' and BRANCH_CODE = ''VN0010338'' and BI_SEGMENT = ''SMEs''))
	and (SEGMENT not in (''KHCN'',''HH'') and (BRANCH_CODE not in (''VN0010289'',''VN0010344'',''VN0010011'',''VN0010012'',''VN0010006'',''VN0010007'',''VN0010002'',''VN0010003'',''VN0010010'',''VN0010324'',''VN0010325'',''VN0010335'',''VN0010336'',''VN0010005'',''VN0010242'',''VN0010009'',''VN0010001'',''VN0010004'',''VN0010008'',''VN0010260'',''VN0010326'',''VN0010327'',''VN0010245'',''VN0010136'') or (SEGMENT is null and LEFT(sector,1)>1)))
	and APP = ''LOAN'' 
	and DAO is not null
	and CIF collate DATABASE_DEFAULT not in (select cif from SME2017DAILY.DBO.BADBANK_'+@DATE_NOW+' where CIF is not null) 
	and (CHUONG_TRINH_SP is null or CHANNEL is null or (CHUONG_TRINH_SP not in (''LD-13-SME CLEAN'',''LD-07-SME-BIL'',''LD-766-MINI BIL'',''LD-762-SME-BIL2', 'LD-875-SME-BIL AUTO', 'LD-876-SME-BIL TAI CAP'',''LD-934-SME BIL EWS'')
	and CHUONG_TRINH_SP not like ''%BIL%'') or (channel in (''DSASME'')  and datediff(day, APP_REJ_DATE, '''+@DATE_NOW_1+''')>90) or Channel not in (''BDO1'',''BDO2'',''BDO3'',''BDO4'',''BDO5'',''MBD1'',''MBD2'',''MBD3'',''MBD4'',''MBD5'',''DSASME''))
	and (CHUONG_TRINH_SP in (''LD-13-SME CLEAN'',''LD-07-SME-BIL'',''LD-766-MINI BIL'',''LD-762-SME-BIL2', 'LD-875-SME-BIL AUTO', 'LD-876-SME-BIL TAI CAP'',''LD-934-SME BIL EWS'') or 
	(Product_name = ''6. Overdraft'' and PRODUCT_DETAIL =''6.3. Unsecured Overdraft'') or CHUONG_TRINH_SP like ''%BIL%'') and CIF collate DATABASE_DEFAULT not in (select cif_dacthu from SME2017.DBO.CIF_DACTHU_'+@YEARMONTH_LAST_MONTH+' where CIF_DACTHU is not null)
	AND DAO collate DATABASE_DEFAULT in (select DAO from SME2017.DBO.HR_SME_'+@YEARMONTH_LAST_MONTH+')
	AND DAO collate DATABASE_DEFAULT in (select DAO from SME2017.DBO.KPI_TARGET_201704_CHANGE)
	order by branch_code,DAO
	
')

--- 4.2 DƯ NỢ BQ
exec('
SELECT  ''+@DATE_NOW+'' BUSINESS_DATE, BRANCH_CODE, APP, DAO ,DAO_CIF ,ACCTNO, CIF, AVGBAL_QD,PRODUCT_GROUP, CHUONG_TRINH_SP ,convert(date, ISSUE_DATE)  ISSUE_DATE, CHANNEL,
	(CASE WHEN DAO <> DAO_CIF  AND CIF COLLATE DATABASE_DEFAULT NOT IN
	(SELECT CIF FROM  SME2017.DBO.CIF_NGOAILE_'+@YEARMONTH_LAST_MONTH+' where CIF is not null) THEN ''KHONG GHI NHAN DO DAO CIF KHAC DAO SP'' ELSE '''' END ) NOTE
	into SME2017DAILY.dbo.RAW_DU_NO_BQ_'+@DATE_NOW+'
	FROM SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme
	WHERE (F is null or F =''B'' or (F= ''Y'' and BRANCH_CODE = ''VN0010338'' and BI_SEGMENT = ''SMEs''))
	and (SEGMENT not in (''KHCN'',''HH'') and (BRANCH_CODE not in (''VN0010289'',''VN0010344'',''VN0010011'',''VN0010012'',''VN0010006'',''VN0010007'',''VN0010002'',''VN0010003'',''VN0010010'',''VN0010324'',''VN0010325'',''VN0010335'',''VN0010336'',''VN0010005'',''VN0010242'',''VN0010009'',''VN0010001'',''VN0010004'',''VN0010008'',''VN0010260'',''VN0010326'',''VN0010327'',''VN0010245'',''VN0010136'') or (SEGMENT is null and LEFT(sector,1)>1)))
	and APP = ''LOAN''
	AND ((channel in (''DSASME'')  and datediff(day, APP_REJ_DATE, '''+@DATE_NOW_1+''')>90 ) or Channel not in  (''BDO1'',''BDO2'',''BDO3'',''BDO4'',''BDO5'',''MBD1'',''MBD2'',''MBD3'',''MBD4'',''MBD5'',''DSASME'') or channel is null)
	and DAO is not null
	and CIF collate DATABASE_DEFAULT not in (select cif from SME2017DAILY.DBO.BADBANK_'+@DATE_NOW+' where CIF is not null)
	and CIF collate DATABASE_DEFAULT not in (select cif_dacthu from SME2017.DBO.CIF_DACTHU_'+@YEARMONTH_LAST_MONTH+' where CIF_DACTHU is not null) 
	AND DAO collate DATABASE_DEFAULT in (select DAO from SME2017.DBO.HR_SME_'+@YEARMONTH_LAST_MONTH+')
	AND DAO collate DATABASE_DEFAULT in (select DAO from SME2017.DBO.KPI_TARGET_201704_CHANGE)
	order by branch_code,DAO
	
')

--- 4.3 Huy động BQ 
exec('
SELECT ''+@DATE_NOW+'' BUSINESS_DATE, BRANCH_CODE, APP, DAO ,DAO_CIF ,ACCTNO, CIF, AVGBAL_QD,PRODUCT_GROUP, convert(date, ISSUE_DATE)  ISSUE_DATE ,
(CASE WHEN DAO <> DAO_CIF  AND CIF COLLATE DATABASE_DEFAULT NOT IN
(SELECT CIF FROM  .SME2017.DBO.CIF_NGOAILE_'+@YEARMONTH_LAST_MONTH+' where CIF is not null ) THEN ''KHONG GHI NHAN DO DAO CIF KHAC DAO SP'' ELSE '''' END ) NOTE 
	into SME2017DAILY.dbo.RAW_HUY_DONG_BQ_'+@DATE_NOW+'
	FROM SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme
	WHERE (F is null or F =''B'' or (F= ''Y'' and BRANCH_CODE = ''VN0010338'' and BI_SEGMENT = ''SMEs''))
	and (SEGMENT not in (''KHCN'',''HH'') and (BRANCH_CODE not in (''VN0010289'',''VN0010344'',''VN0010011'',''VN0010012'',''VN0010006'',''VN0010007'',''VN0010002'',''VN0010003'',''VN0010010'',''VN0010324'',''VN0010325'',''VN0010335'',''VN0010336'',''VN0010005'',''VN0010242'',''VN0010009'',''VN0010001'',''VN0010004'',''VN0010008'',''VN0010260'',''VN0010326'',''VN0010327'',''VN0010245'',''VN0010136'') or (SEGMENT is null and LEFT(sector,1)>1)))
	and APP = ''DEPOSIT''
	and DAO is not null
	and (channel <> ''SMETLS'' and channel <> ''PSASME'' or channel is null)  ---Channel CTV 
	and CIF collate DATABASE_DEFAULT not in (select cif_dacthu from SME2017.DBO.CIF_DACTHU_'+@YEARMONTH_LAST_MONTH+'  where CIF_DACTHU is not null)
	and (DAO collate DATABASE_DEFAULT = DAO_CIF or CIF collate DATABASE_DEFAULT in (select CIF from SME2017.DBO.CIF_NGOAILE_'+@YEARMONTH_LAST_MONTH+' where CIF is not null))
	AND DAO collate DATABASE_DEFAULT in (select DAO from SME2017.DBO.HR_SME_'+@YEARMONTH_LAST_MONTH+')
	AND DAO collate DATABASE_DEFAULT in (select DAO from SME2017.DBO.KPI_TARGET_201704_CHANGE)
	order by branch_code,DAO
	
')

--- 4.4 Số lượng KHHĐ
--- 4.4.1 -- KHHD tăng thêm
exec('
SELECT ''+@DATE_NOW+'' BUSINESS_DATE, DAO, SO_LUY_KE, DEC_2016, BAN_GIAO, NHAN_BAN_GIAO, SO_MOI as SO_KHHD_TANG_THEM
	INTO SME2017DAILY.dbo.RAW_KHHD_TANG_THEM_'+@DATE_NOW+'
	FROM SME2017DAILY.DBO.KHACH_HANG_'+@DATE_NOW+' 
	WHERE  DAO collate DATABASE_DEFAULT in (select DAO from SME2017.DBO.HR_SME_'+@YEARMONTH_LAST_MONTH+')
	AND DAO collate DATABASE_DEFAULT in (select DAO from SME2017.DBO.KPI_TARGET_201704_CHANGE)
	order by DAO

--- Chi tiết KHHĐ
--- Ngày chốt dữ liệu
SELECT ''+@DATE_NOW+'' BUSINESS_DATE ,RECID,CUS_NAME, CUS_OPEN_DATE, DAO, COMPANY_BOOK
	INTO SME2017DAILY.dbo.RAW_KHHD_'+@DATE_NOW+'
	FROM .SME2017DAILY.DBO.VPB_CUSTOMER_'+@DATE_NOW+'
	WHERE  RECID COLLATE DATABASE_DEFAULT IN
	(SELECT CIF FROM SME2017DAILY.DBO.KHHD_'+@DATE_NOW+') 
	AND DAO IS NOT NULL
	AND  ((channel in (''DSASME'')  and datediff(day, CUS_OPEN_DATE, '''+@DATE_NOW_1+''')>120 ) or Channel not in  (''BDO1'',''BDO2'',''BDO3'',''BDO4'',''BDO5'',''MBD1'',''MBD2'',''MBD3'',''MBD4'',''MBD5'',''DSASME'') or channel is null)
	AND SEGMENT=''SMES''
	AND DAO collate DATABASE_DEFAULT in (select DAO from HR_SME_'+@YEARMONTH_LAST_MONTH+')
	AND DAO collate DATABASE_DEFAULT in (select DAO from KPI_TARGET_201704_CHANGE)
	order by branch,DAO

--- Tháng 12/2016
SELECT ''201612'' BUSINESS_DATE,RECID,CUS_NAME, CUS_OPEN_DATE, DAO, COMPANY_BOOK
	INTO SME2017DAILY.dbo.RAW_KHHD_201612
	FROM .SME2016.DBO.VPB_CUSTOMER_20161231
	WHERE  RECID COLLATE DATABASE_DEFAULT IN
	(SELECT CIF from KHHD_201612 ) 
	AND DAO IS NOT NULL
	AND (CHANNEL COLLATE DATABASE_DEFAULT NOT IN (''PSASME'',''DSASME'') OR CHANNEL IS NULL or DATEDIFF (DAY,CUS_OPEN_DATE, ''2016-12-31'')>120)
	AND SEGMENT=''SMES''
	order by DAO
	
')

--- 4.5 Nợ xấu
exec('
SELECT ''+@DATE_NOW+'' BUSINESS_DATE,BRANCH_CODE, APP, DAO ,DAO_CIF ,ACCTNO, CIF, BAL_QD,PRODUCT_GROUP, CHUONG_TRINH_SP ,convert(date, ISSUE_DATE)  ISSUE_DATE
	into SME2017DAILY.dbo.RAW_NO_XAU_'+@DATE_NOW+'
	FROM SME2017DAILY.DBO.BALANCE_BADBANK_'+@DATE_NOW+'  
	WHERE ACCTNO COLLATE DATABASE_DEFAULT in (select ACCTNO from SME2017DAILY.dbo.balance'+@DATE_NOW+'_sme where (F IS NULL OR F =''B'' or (F= ''Y'' and BRANCH_CODE = ''VN0010338'' and BI_SEGMENT = ''SMEs''))
	AND DAO IS NOT NULL
	AND (SEGMENT not in (''KHCN'',''HH'') and (branch_code not in (''VN0010289'',''VN0010344'',''VN0010011'',''VN0010012'',''VN0010006'',''VN0010007'',''VN0010002'',''VN0010003'',''VN0010010'',''VN0010324'',''VN0010325'',''VN0010335'',''VN0010336'',''VN0010005'',''VN0010242'',''VN0010009'',''VN0010001'',''VN0010004'',''VN0010008'',''VN0010260'',''VN0010326'',''VN0010327'',''VN0010245'',''VN0010136'') or (SEGMENT is null and LEFT(sector,1)>1)))
	AND APP=''LOAN''
	AND CIF COLLATE DATABASE_DEFAULT NOT IN (SELECT CIF FROM SME2016.DBO.BADBANK_20161231)
	AND CIF COLLATE DATABASE_DEFAULT NOT IN (SELECT CIF_DACTHU FROM SME2017.DBO.CIF_DACTHU_'+@YEARMONTH_LAST_MONTH+' where CIF_DACTHU is not null) 
	AND (CHUONG_TRINH_SP IS NULL or (channel in (''DSASME'')  and datediff(day, APP_REJ_DATE, '''+@DATE_NOW_1+''')>90 ) or Channel not in  (''BDO1'',''BDO2'',''BDO3'',''BDO4'',''BDO5'',''MBD1'',''MBD2'',''MBD3'',''MBD4'',''MBD5'',''DSASME'') or channel is null)
	AND ( DAO COLLATE DATABASE_DEFAULT  = DAO_CIF OR CIF COLLATE DATABASE_DEFAULT IN (SELECT CIF FROM  SME2017.DBO.CIF_NGOAILE_'+@YEARMONTH_LAST_MONTH+' where CIF is not null)))
	order by branch_code,DAO
	
')

--- 4.6 Thẻ 
exec('
	drop table #a
	select ACNT_CONTRACT_ID, ACNT_CONTRACT_OID, CONTRACT_NUMBER, CARD_NUMBER, CARD_STATUS, CARD_DATE_OPEN, PRODUCTION_STATUS,
			CARD_TYPE, UNLOCK_DATE, T24_DAO, CONTRACT_CIF, CONTRACT_STATUS, ACTUAL_DATE_EXPIRE into #a from  SME2017DAILY.DBO.CARD_'+@DATE_NOW+'
			where T24_DAO collate DATABASE_DEFAULT in (select DAO from SME2017.DBO.HR_SME_'+@YEARMONTH_LAST_MONTH+') and (unlock_date >= ''2017-01-01'' 
			 or 	(((CONVERT(DATE,CARD_DATE_OPEN) >= ''2016-10-01'' and ACTUAL_DATE_EXPIRE <= ''2017-03-31'' and ACTUAL_DATE_EXPIRE >= ''2017-01-01'') or (CONVERT(DATE,CARD_DATE_OPEN) >= ''2017-01-01''))
	AND ACTUAL_DATE_EXPIRE is not null and ACTUAL_DATE_EXPIRE <= '''+@DATE_NOW_1+'''))

	alter table #a
	add DAO_CIF nvarchar (50)


	update #a
	set DAO_CIF= B.DAO 
	from #a A, SME2017DAILY.DBO.VPB_CUSTOMER_'+@DATE_NOW+' B
	where A.CONTRACT_CIF = B.RECID 

	alter table #a
	add BRANCH nvarchar(50)

	select BRANCH, CONTRACT_NUMBER, CARD_NUMBER, CONTRACT_CIF as CIF, CARD_STATUS,CARD_DATE_OPEN, CARD_TYPE, UNLOCK_DATE, 
	T24_DAO, DAO_CIF, CONTRACT_STATUS, ACTUAL_DATE_EXPIRE, CASE WHEN ACTUAL_DATE_EXPIRE is not null then N''Thẻ đóng'' END NOTE 
	into SME2017DAILY.dbo.RAW_THE_'+@DATE_NOW+'
	from #a

')
	--select top 1 * from M74.BICDATA.DBO.BALANCE_LIVE 

	--select * from [dbo].[VPB_CUSTOMER_'+@DATE_NOW+']     [dbo].[KHACH_HANG_'+@DATE_NOW+'] [dbo].[FINAL_MSBO_'+@DATE_NOW+'_KQ]
 --select * from [dbo].[RAW_KHHD_TANG_THEM_'+@DATE_NOW+']

 --insert server12.kpi_sme.dbo.[KHHD_'+@DATE_NOW+']
 --select * from [dbo].[KHHD_'+@DATE_NOW+']

 --select * from [dbo].[FINAL_MSBO_'+@DATE_NOW+'_KQ]
 
 --    [dbo].[RAW_DU_NO_BQ_'+@DATE_NOW+']

	-- select * from SME2017DAILY.DBO.FINAL_MSBO_'+@DATE_NOW+'_KQ